<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="ko" xml:lang="ko">

<head>
	<title></title>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
	<%@page import = "java.util.*,java.text.*,java.io.*"%>
	<link rel="stylesheet" type="text/css" href="../css/base.css" media="all" />
	<script language="javascript" src="calendar.js"></script>
<script type="text/javascript">
function loginChk(){
	alert("not login");
	location.href("../mkt/main");
	return false;
}

function doValidate1(){
	var start = document.form1.start.value;
	var end = document.form1.end.value;
	var slots = document.form1.slots.value;
	var company = document.form1.company.value;
	
	if(start == "" || end == "")
	{
		alert("date is an empty value.");
		document.form1.start.focus();
		return false ;
	}
	
	if((start.charAt(4) != '-' || start.charAt(7) != '-') || (end.charAt(4) != '-' || end.charAt(7) != '-'))
	{
		alert("The date format was wrong.");
		document.form1.start.focus();
		return false ;
	}

	if(start.length != 10 || end.length != 10)
	{
		alert("The date format was wrong.");
		document.form1.start.focus();
		return false ;
	}
	
	if((start.substring(0,4) <= 1899) || (start.substring(0,4) >= 3000) || (end.substring(0,4) <= 1899) || (end.substring(0,4) >= 3000))
	{
		alert("The date format was wrong.");
		document.form1.start.focus();
		return false ;
	}
	
	if((start.substring(5,7) <= 0) || (start.substring(5,7) >= 13) || (end.substring(5,7) <= 0) || (end.substring(5,7) >= 13))
	{
		alert("The date format was wrong.");
		document.form1.start.focus();
		return false ;
	}
	
	if((start.substring(8,10) <= 0) || (start.substring(8,10) >= 32) || (end.substring(8,10) <= 0) || (end.substring(8,10) >= 32))
	{
		alert("The date format was wrong.");
		document.form1.start.focus();
		return false ;
	}
	if(slots == null || slots == '')
	{
		alert("Slots is null.");
		document.form1.slots.focus();
		return false ;
	}
	
	if(isNaN(slots) == true)
	{
		alert("Not the number");
		document.form1.slots.focus();
		return false ;
	}
	document.form1.company.value = company;
	document.form1.submit();
}
</script>
</head>
<body id="wrap_bg">
<div id="accessibility">
	<h1 class="hidden"><a href="/">logo</a></h1>	
</div>
<div id="wrap">
	<h2 class="hidden">main_menu</h2>
	<div id="container">
		<!-- left-->		
	    <h2 class="hidden">sub_menu</h2>
		<div id="left_part">
			<div id="left_menu"></div>
		</div>
		<h2 class="hidden">contacts</h2>
		<div id="body_part">
			<div id="body_con">
<%

String id = "";
String start = "";
String end = "";
String company = "";
String dayprice = "";
String slots = "";
String unitprice = "";

if(request.getParameter("id") == null){
	out.println("<script>loginChk()</script>");
}

id = request.getParameter("id");
company = request.getParameter("company");
dayprice = request.getParameter("dayprice");

if(request.getParameter("start") != null){
	start = request.getParameter("start");
	end = request.getParameter("end");
	slots = request.getParameter("slots");
	unitprice = request.getParameter("unitprice");
}

Date sdate = new Date();
SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd");
String strDate = simpleDate.format(sdate);

Calendar eDate = Calendar.getInstance();
eDate.add(Calendar.DAY_OF_MONTH,+6);
Date enDate = eDate.getTime();
String endDate = new SimpleDateFormat("yyyy-MM-dd").format(enDate);

String connectionUrl = "jdbc:sqlserver://bizring.biz:14333;databaseName=bizring";
String userid = "sa";
String passwd = "qlalf@sql";

// Declare the JDBC objects.
java.sql.Connection con = null;
java.sql.Statement stmt = null;
java.sql.Statement stmt2 = null;
java.sql.ResultSet rs = null;
java.sql.ResultSet rs2 = null;

NumberFormat nf = NumberFormat.getNumberInstance();
// Establish the connection.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
%>
			<p class="title" style="padding:72px 0 16px;"><a href="./price_advertiser.html?id=<%=id%>"><img src="../images/3s/bizring.jpg" alt="bizring" width="110" height="40"/></a>
			&nbsp;&nbsp;&nbsp;<img src="../images/3s/sss.jpg" alt="bizring" width="110" height="20"/>
				<div class="ta_top"></div>
				<div class="ta_bg">
					<div class="bs_con">
					<div class="clear" style="padding-top:15px;text-align:left;">
					<a href="./price_advertiser.html?id=<%=id%>"><img src="../images/3s/main.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/chkavailable.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/download.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/faq.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/qna.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/adschedule.jpg" alt="send" align="absmiddle" width="80" height="30"></a>
					<a href="#" onclick=""><img src="../images/3s/checkreport.jpg" alt="send" align="absmiddle" width="80" height="30"></a></div>
				<br/><br/>
<%
	try {
		
		// Create and execute an SQL statement that returns some data.
		String SQL = "select count(*) as totcnt from cust_master where status = 'W'";
		
		con = java.sql.DriverManager.getConnection(connectionUrl, userid, passwd);
		stmt = con.createStatement();
		rs = stmt.executeQuery(SQL);

		rs.next();
		
		int num = rs.getInt("totcnt");
		String nfCnt = nf.format(num);
%>
		<form action="./price_estimation.html" name="form1" method="post">
			<table cellpadding="0">
			<tr>
<%if(start == null || start.equals("")){%>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Advertiser&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="company" id="company" value="<%=company %>" size="15" readonly/>
				</td>
				<td class="date"><span style='font-size:10.0pt;color:black;text-align:center;'><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Start&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="start" id="start" value="<%=strDate %>" size="9" onclick="Calendar(this, event)"/></td><td>&nbsp;&nbsp;&nbsp;~&nbsp;&nbsp;&nbsp;</td>
				<td class="date"><span style='font-size:10.0pt;color:black;text-align:center;'><strong>End&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="end" id="end" value="<%=endDate %>" size="9" onclick="Calendar(this, event)"/></td>
<%}else{ %>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Advertiser&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="company" id="company" value="<%=company %>" size="15" readonly/>
				</td>
				<td class="date"><span style='font-size:10.0pt;color:black;text-align:center;'><strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Start&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="start" id="start" value="<%=start %>" size="9" onclick='Calendar(this, event)'/></td><td>&nbsp;&nbsp;&nbsp;~&nbsp;&nbsp;&nbsp;</td>
				<td class="date"><span style='font-size:10.0pt;color:black;text-align:center;'><strong>End&nbsp;&nbsp;&nbsp;</strong></span>
				<input class=form_text02 type="text" name="end" id="end" value="<%=end %>" size="9" onclick='Calendar(this, event)'/></td>
<%} %>
			</tr>
			<tr>
				<input type="hidden" name="id" id="id" value="<%=id %>" size="15"/>
				<input type="hidden" name="dayprice" id="dayprice" value="<%=dayprice %>" size="15"/>
			</tr>
			</table>
			<br/>
			<table>
<%if(start == null || start.equals("")){%>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Your Unit Price &nbsp;&nbsp;&nbsp;</strong></span><input class=form_text02 type="text" name=unitprice id=unitprice value="<%=dayprice%>" size="3"/></td>
<%}else{ %>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Your Unit Price &nbsp;&nbsp;&nbsp;</strong></span><input class=form_text02 type="text" name=unitprice id=unitprice value="<%=unitprice%>" size="3"/></td>
<%} %>
			</table>
			<br/>
			<table cellpadding="20">
			<tr>
<%if(start == null || start.equals("")){%>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Slots / day&nbsp;&nbsp;&nbsp;&nbsp;</strong></span><input class=form_text02 type="text" name="slots" id="slots" value="" size="15"/></td>
<%}else{ %>
				<td><span style='font-size:10.0pt;color:black;text-align:center;'><strong>Slots / day&nbsp;&nbsp;&nbsp;&nbsp;</strong></span><input class=form_text02 type="text" name="slots" id="slots" value="<%=slots %>" size="15"/></td>
<%} %>
				<td valign="middle"><span style='font-size:10.0pt;color:black;text-align:center;'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Current is <strong><%=nfCnt%></strong> slot available per day.&nbsp;&nbsp;&nbsp;</span>
				<a href="#" onclick="doValidate1()"><img src="../images/3s/run.jpg" alt="send"/></td>
			</tr>
			</table>
		</form>
		<br/><br/>
		
<%
	} catch (java.sql.SQLException e) {   
	    e.printStackTrace();  
	} finally{   
	    try {   
	        if(stmt !=null) stmt.close();   
	        if(con !=null) con.close();   
	    } catch (java.sql.SQLException e) {   
	        e.printStackTrace();   
	    }   
	}  
	
	if(start != null && !start.equals(""))
	{
		if(unitprice != null && !unitprice.equals(""))
		{
			dayprice = unitprice;
		}
		
		try {   
			// Create and execute an SQL statement that returns some data.
			String SQL = "select ((convert(varchar(11),ad_date,113))+ '(' + day + ')') as date																																" +
			",(case                                                                                                                                             															" +
			"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') <= 3 then ''                                        	 			" +
			"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') > 3 then '-30%'                                            		" +
			"end) as discount                                                                                                                                   															" +
			",(case rate                                                                                                                                        															" +
			"when '1.3' then '+30%'                                                                                                                             															" +
			"when '1'   then ''                                                                                                                                 															" +
			"end) as premium                                                                                                                                    															" +
			",'" + slots + "' as slots                                                                                                                                    							" +
			",(case                                                                                                                                             															" +
			//"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') <= 3 then ((" + slots + "*(dayprice*rate)))      " +
			//"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') > 3 then (((" + slots + "*(dayprice*rate))*0.7)) " +
			"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') <= 3 then ((" + slots + "*(" + dayprice + "*rate)))" +
			"when (select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') > 3 then (((" + slots + "*(" + dayprice + "))*0.7))" +
			"end) as price																																																	" +
			",(select COUNT(*) from seller_rate where ad_Date between '" + start + "' and '" + end + "') as cnt																" +
			"from seller_rate as r ,seller as p                                                                                                                                          								" +
			"where r.ad_Date between '" + start + "' and '" + end + "'                                                                                             			" +
			"  and p.id='" + id + "'																																								" +
			"group by r.ad_date,r.day,r.rate,p.dayprice                                                                                                                          											" ;
			
			String SQL2 = "select COUNT(*) as cnt from seller_rate where ad_Date between '" + start + "' and '" + end + "'";
			
			con = java.sql.DriverManager.getConnection(connectionUrl, userid, passwd);
			stmt = con.createStatement();
			rs = stmt.executeQuery(SQL);
			stmt2 = con.createStatement();
			rs2 = stmt2.executeQuery(SQL2);
			// Iterate through the data in the result set and display it.
			rs2.next();
			
			long totalPrice = 0;
			
			//long totalSlots = 0;
			
			//for make csv file
			String fullPath = "/tomcat/webapps/ROOT/3s/";
			StringBuffer sb = new StringBuffer();
			String fileName = "estimation.csv";
			
			FileOutputStream fos = new FileOutputStream(fullPath+fileName);
			DataOutputStream dos = new DataOutputStream(fos);
			OutputStreamWriter osw = new OutputStreamWriter(dos,"MS949");
			
			if(rs.isBeforeFirst() == true){
%>
<div id='print_table'>
<table style='width:430pt;margin-left:4.2pt;'>
 <tr>
 	<td colspan="4"><span style='font-size:13.0pt;text-align:center;color:black'>Your Unit Price : Rp.<%=dayprice %></span><br/><br/></td>
 </tr>
 <tr>
 	<td colspan="4"><span style='font-size:11.0pt;text-align:center;'>Advertiser  : <%=company %></span></td>
 </tr>
 <tr>
 	<td colspan="4"><span style='font-size:11.0pt;text-align:center;'>AD Duration : <%=start%> ~ <%=end%>&nbsp;&nbsp;(<%=rs2.getString("cnt")%>)</span><br/><br/></td>
 </tr>
 <tr style='height:16.5pt'>
  <td style='width:100.0pt;border:solid windowtext 1.0pt;background:#FDE9D9;padding:0 5pt 0 5pt'>
  <p style='font-size:11.0pt;text-align:center;color:black'>Date</p>
  </td>
  <td style='width:25pt;border:solid windowtext 1.0pt;background:#FDE9D9;padding:0 5pt 0 5pt'>
  <p style='font-size:11.0pt;color:black;'>Discount</p>
  </td>
  <td style='width:25.0pt;border:solid windowtext 1.0pt;background:#FDE9D9;padding:0 5pt 0 5pt'>
  <p style='font-size:11.0pt;text-align:center;color:black'>Premium</p>
  </td>
  <td style='width:50.0pt;border:solid windowtext 1.0pt;background:#FDE9D9;padding:0 5pt 0 5pt'>
  <p style='font-size:11.0pt;text-align:center;color:black'>Slots</p>
  </td>
  <td style='width:100.0pt;border:solid windowtext 1.0pt;background:#FDE9D9;padding:0 5pt 0 5pt'>
  <p style='font-size:11.0pt;text-align:center;color:black'>Price</p>
  </td>
 </tr>
<%
sb.append("Your Unit Price : Rp." + dayprice + " \r\n");
sb.append("Advertiser : " + company + "\r\n");
sb.append("AD Duration : " + start + "   ~   " + end + "\r\n\r\n");
sb.append("Date , Discount , Premium , Slots , Price \r\n");
			}
			
			while(rs.next()){
				
				long price = rs.getLong("price");
				//long slots = rs.getLong("slots");
				
				totalPrice += price;
				//totalSlots += slots;

				/* out.println(rs.getString("date"));
				out.println(rs.getString("discount"));
				out.println(rs.getString("premium"));
				out.println(rs.getString("slots"));
				out.println(rs.getString("price")); */
%>
 <tr>
  <td style='width:100.0pt;border:solid windowtext 1.0pt;padding:0 5pt 0 5pt;'>
  	<p style='font-size:10.0pt;color:black;text-align:center;'><%=rs.getString("date")%></p>
  </td>
  <td style='width:25pt;border:solid windowtext 1.0pt;padding:0 5pt 0 5pt'>
  	<p style='font-size:10.0pt;text-align:center;'><%=rs.getString("discount")%></p>
  </td>
  <td style='width:25.0pt;border:solid windowtext 1.0pt;padding:0 5pt 0 5pt'>
  	<p style='font-size:10.0pt;text-align:center;color:black'><%=rs.getString("premium")%></p>  
  </td>
  <td style='width:50.0pt;border:solid windowtext 1.0pt;padding:0 5pt 0 5pt'>
  	<p style='font-size:10.0pt;text-align:center;color:black'><%=nf.format(Integer.parseInt(slots))%></p>  
  </td>
  <td style='width:100.0pt;border:solid windowtext 1.0pt;padding:0 5pt 0 5pt'>
  	<p style='font-size:10.0pt;text-align:center;color:red'>Rp.<%=nf.format(price)%></p>  
  </td>
 </tr>
<%
sb.append(rs.getString("date") + "," + rs.getString("discount") + "," + rs.getString("premium") + "," + rs.getString("slots") + ",Rp." + price + "\r\n");
			}
%>
 <tr>
 	<td colspan="5" align="right"><span style='font-size:11.0pt;text-align:right;color:black'><br/><strong>Total price</strong></span></td>
 </tr>
 <tr>
 	<td colspan="5" align="right"><span style='font-size:10.0pt;text-align:right;'><strong><%=rs2.getString("cnt")%></strong>&nbsp;Days</span></td>
 </tr>
 <tr>
 	<td colspan="5" align="right"><span style='font-size:10.0pt;text-align:right;'><strong><%=nf.format(Integer.parseInt(slots))%></strong>&nbsp;Slots</span></td>
 </tr>
 <tr>
 	<td colspan="5" align="right"><span style='font-size:10.0pt;text-align:right;'>Rp.<strong><%=nf.format(totalPrice)%></strong></span><br/><br/></td>
 </tr>
 </table>
</div> 
 <a href="#" onclick="window.open('3s_print.jsp','print_win','width=700,height=400,left=200,status=no,toolbar=no,resizable=no,scrollbars=yes')"><img src="../images/3s/printbt.jpg" alt="print" align="absmiddle"></a>
&nbsp;&nbsp;<a href="./estimation.csv"><img src="../images/3s/savebt.jpg" alt="save" align="absmiddle"></a>
<%
sb.append("\r\n ,,,," + rs2.getString("cnt") + " Days \r\n");
sb.append(",,,," + totalPrice + " Slots \r\n");
sb.append(",,,,Rp." + totalPrice + "\r\n");

			osw.write(sb.toString(), 0, sb.length());
			sb.delete(0, sb.length()-1);
			osw.close();
			dos.close();
			fos.close();

		} catch (java.sql.SQLException e) {   
		    e.printStackTrace();  
		} catch (IOException ioe){
			ioe.printStackTrace();   
		}finally{
		    try {   
		        if(stmt !=null) stmt.close();
		        if(stmt2 !=null) stmt.close();
		        if(con !=null) con.close();
		    } catch (java.sql.SQLException e) {   
		        e.printStackTrace();   
		    }   
		}
	}
%>
					</div>					
				</div>
				<div class="ta_bot"></div>
			</div>		
		</div>
	</div>

	<h2 class="hidden">copyright</h2>
	<div id="foot">
		<div id="foot_part">
			<p id="line_solid"></p>
			<div class="foot_adress">
				<ul>
					<li class="logo"><img src="../images/common/tsellogo.png" alt="Powered by TELKOMSEL" /></li>
					<li class="address">
						<div class="f_address">
							<address>
							<img src="../images/common/copyright.gif" alt="This site is The Telkomsel Small Ring Service website.   /   BizRing Operator : PT. Kami Kaya Kreasi
Address : Jl. Daksa IV, No. 69, Kebayoran Baru, Jakarta Selatan - 12110 Indonesia
Tel +62 21 723 5038  Fax +62 21 723 5037  info@kamikayakreasi.com
wwww.kamikayakreasi.com" />
							</address>					
						</div>
					</li>
				</ul>
			</div>	
		</div>
	</div>
 
</div>
</BODY>
</HTML>
